package junit;

import java.sql.*;

import org.junit.*;
import static org.junit.Assert.*;

import fast.JDBC;

public class JDBCTest {

	private Connection con;
	private JDBC jdbc;
	private ResultSet rs;
	public String data = "this is JDBCTest!";
	public String slaveIP = "127.0.0.1";
	public int SID = 1000;
	public int FID = 1000;
	public PreparedStatement stmt;
	public String filePathFH = "C:\\users\\test.iso";
	public String searchStringFH = "testies";

	// Tests openDB in JDBC
	@Test
	public void check_openAndCloseDB() throws SQLException, Exception {
		System.out.println("Test: check_openAndCloseDB 1 of 2");
		System.out.println("******************");
		// Blank line
		System.out.println("");

		// Prints the test about to run
		System.out.println("SELECT * FROM resultsCombined");

		try {
			// testing that the openDB command runs
			if (jdbc.openDB()) {
				// Running a select statement to retrieve data from the database
				stmt = con.prepareStatement("SELECT * FROM user");
				// Runs the select query
				rs = stmt.executeQuery();

				// Output success for query being executed
				System.out
						.println("Successfully ran query: SELECT * FROM user");
				System.out.println("");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println("Test: check_openAndCloseDB 2 of 2");
		System.out.println("**************************");

		if (jdbc.closeDB()) {
			System.out.println("Successfully ran closeDB");
			System.out.println("");
		}
	}

	// Tests resultsWrite in JDBC
	@Test
	public void check_resultsWrite() throws SQLException, Exception {

		String[] findRID = new String[2];
		int recordsUpdated = 0;
		int recordsUpdated2 = 0;
		String[][] data1 = new String[2][2];
		String[][] data2 = new String[1][2];

		data1[0][0] = "1.1.1.1";
		data1[0][1] = "test";
		data1[1][0] = "2.2.2.2";
		data1[1][1] = "test";
		data2[0][0] = "3.3.3.3";
		data2[0][1] = "test";

		System.out.println("Test: check_resultsWrite");
		System.out.println("************************");
		// Blank line
		System.out.println("");
		
		System.out.println("CHECK WITH ONE RESULTS IN THE ARRAY");
		// run resultsWrite with preset data, slaveIP, SID and FID
		if (jdbc.openDB()) {
			jdbc.resultsWrite(data2, SID, FID);
		}

		// Now to check that resultsWrite successfully worked
		try {
			// Blank line
			System.out.println("");
			System.out.println("Step 1: Checking row was entered into results");

			// Run a select statement retrieving the exact information we
			// passed into resultsWrite.
			for (int i = 0; i < data2.length; i++) {
				System.out.println("Select * from results "
						+ "where slaveIP = '" + data2[i][0] + "' and data = '"
						+ data2[i][1] + "'");

				stmt = con.prepareStatement("SELECT * FROM results "
						+ "where slaveIP = '" + data2[i][0] + "' and data = '"
						+ data2[i][1] + "'");
				// Output row data where the slaveIP and data matches the test
				rs = stmt.executeQuery();
				if (rs.next()) {
					int numColumns = rs.getMetaData().getColumnCount();

					for (int i1 = 1; i1 <= numColumns; i1++) {
						System.out.println("results: COLUMN " + "=" + " "
								+ rs.getObject(i1));
					}

					System.out.println("END OF RESULTS");
					// Blank line
					System.out.println("");
				} else {
					System.out.println("FAILED: No match was found with stmt");
					// Blank line
					System.out.println("");
				}
			}

			System.out.println("Step 2: Selecting RID from results");
			// Now select the RID for the row of data that was added by
			// resultsWrite
			for (int i = 0; i < data2.length; i++) {
				stmt = con.prepareStatement("SELECT RID FROM results "
						+ "where slaveIP = '" + data2[i][0] + "' and data = '"
						+ data2[i][1] + "'");

				System.out.println("SELECT RID FROM results "
						+ "where slaveIP = '" + data2[i][0] + "' and data = '"
						+ data2[i][1] + "'");

				// Get the ID of the row entered
				rs = stmt.executeQuery();
				while (rs.next()) {

					int numColumns = rs.getMetaData().getColumnCount();

					for (int i1 = 1; i1 <= numColumns; i1++) {
						findRID[i] = rs.getObject(i1).toString();
						System.out.println("results ID is = " + findRID[i]);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			// Blank line
			System.out.println("");
			System.out
					.println("Step 3: Delete the records from results and resultsComdbined");
			// Delete new row from the results table
			for (int i = 0; i < findRID.length; i++) {
				stmt = con.prepareStatement("DELETE FROM results WHERE RID = '"
						+ findRID[i] + "'");
				System.out.println("DELETE FROM RESULTS WHERE RID = '"
						+ findRID[i] + "'");
				stmt.executeUpdate();

				// Delete new row from resultsCombined table
				stmt = con
						.prepareStatement("DELETE FROM resultsCombined WHERE RID = '"
								+ findRID[i] + "'");

				stmt.executeUpdate();

			}
			// Blank line
			System.out.println("");

			// Completed
			System.out.println("check_resultsWrite COMPLETED");
			// Blank line
			System.out.println("");
		}
		
		System.out.println("");
		System.out.println("CHECK WITH MORE THEN ONE ENTRY OF DATA INTO THE ARRAY");
		// run resultsWrite with preset data, slaveIP, SID and FID
		if (jdbc.openDB()) {
			jdbc.resultsWrite(data1, SID, FID);
		}

		// Now to check that resultsWrite successfully worked
		try {
			// Blank line
			System.out.println("");
			System.out.println("Step 1: Checking row was entered into results");

			// Run a select statement retrieving the exact information we
			// passed into resultsWrite.
			for (int i = 0; i < data1.length; i++) {
				System.out.println("Select * from results "
						+ "where slaveIP = '" + data1[i][0] + "' and data = '"
						+ data1[i][1] + "'");

				stmt = con.prepareStatement("SELECT * FROM results "
						+ "where slaveIP = '" + data1[i][0] + "' and data = '"
						+ data1[i][1] + "'");
				// Output row data where the slaveIP and data matches the test
				rs = stmt.executeQuery();
				if (rs.next()) {
					int numColumns = rs.getMetaData().getColumnCount();

					for (int i1 = 1; i1 <= numColumns; i1++) {
						System.out.println("results: COLUMN " + "=" + " "
								+ rs.getObject(i1));
					}

					System.out.println("END OF RESULTS");
					// Blank line
					System.out.println("");
				} else {
					System.out.println("FAILED: No match was found with stmt");
					// Blank line
					System.out.println("");
				}
			}

			System.out.println("Step 2: Selecting RID from results");
			// Now select the RID for the row of data that was added by
			// resultsWrite
			for (int i = 0; i < data1.length; i++) {
				stmt = con.prepareStatement("SELECT RID FROM results "
						+ "where slaveIP = '" + data1[i][0] + "' and data = '"
						+ data1[i][1] + "'");

				System.out.println("SELECT RID FROM results "
						+ "where slaveIP = '" + data1[i][0] + "' and data = '"
						+ data1[i][1] + "'");

				// Get the ID of the row entered
				rs = stmt.executeQuery();
				while (rs.next()) {

					int numColumns = rs.getMetaData().getColumnCount();

					for (int i1 = 1; i1 <= numColumns; i1++) {
						findRID[i] = rs.getObject(i1).toString();
						System.out.println("results ID is = " + findRID[i]);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			// Blank line
			System.out.println("");
			System.out
					.println("Step 3: Delete the records from results and resultsComdbined");
			// Delete new row from the results table
			for (int i = 0; i < findRID.length; i++) {
				stmt = con.prepareStatement("DELETE FROM results WHERE RID = '"
						+ findRID[i] + "'");
				System.out.println("DELETE FROM RESULTS WHERE RID = '"
						+ findRID[i] + "'");
				stmt.executeUpdate();

				// Delete new row from resultsCombined table
				stmt = con
						.prepareStatement("DELETE FROM resultsCombined WHERE RID = '"
								+ findRID[i] + "'");

				stmt.executeUpdate();

			}
			// Blank line
			System.out.println("");

			// Completed
			System.out.println("check_resultsWrite COMPLETED");
			// Blank line
			System.out.println("");
		}

	}

	// Tests setFilePathID in JDBC
	@Test
	public void check_setFilePathID() throws SQLException, Exception {

		int recordsUpdated = 0;
		System.out.println("Test: check_setFilePathID");
		System.out.println("*************************");
		// Blank line
		System.out.println("");

		// run setFilePathID with preset filePathFH, FH stands for "from host"
		if (jdbc.openDB()) {
			FID = jdbc.setFilePathID(filePathFH);
		}

		// Now to check that setFilePathID successfully worked
		try {
			// Blank line
			System.out.println("");
			System.out.println("filePath to match against is = " + filePathFH);
			// Blank line
			System.out.println("");
			System.out.println("Step 1: Checking SID returned by setFilePathID"
					+ " contains same filePathFH");

			// Run a select statement retrieving the filePath associated with
			// that FID
			System.out.println("Select * from file where FID = '" + FID + "'");

			stmt = con.prepareStatement("Select * from file where FID = '"
					+ FID + "'");

			// Output row data where the slaveIP and data matches the test
			rs = stmt.executeQuery();
			if (rs.next()) {
				int numColumns = rs.getMetaData().getColumnCount();

				for (int i = 1; i <= numColumns; i++) {
					System.out.println("results: COLUMN " + "=" + " "
							+ rs.getObject(i));
				}

				System.out.println("END OF RESULTS");
				// Blank line
				System.out.println("");
			} else {
				System.out.println("FAILED: No match was found with FID");
				// Blank line
				System.out.println("");
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {

		}

		// Blank line
		System.out.println("");
		System.out.println("Step 3: Delete the row which has been entered "
				+ "with filePathFH");
		// Delete new row from the results table
		stmt = con.prepareStatement("DELETE FROM file WHERE FID = '" + FID
				+ "'");

		recordsUpdated = stmt.executeUpdate();

		System.out.println("Number of fields deleted from results = "
				+ recordsUpdated);

		// Blank line
		System.out.println("");

		// Completed
		System.out.println("check_setFilePathID COMPLETED");

		// Blank line
		System.out.println("");

	}

	// Test setSearchStringID
	@Test
	public void check_setSearchStringID() throws SQLException, Exception {

		int recordsUpdated = 0;

		System.out.println("Test: check_setSearchStringID");
		System.out.println("*****************************");
		// Blank line
		System.out.println("");

		// run setSearchStringID with preset searchStringFH, FH stands for
		// "from host"
		if (jdbc.openDB()) {
			SID = jdbc.setSearchStringID(searchStringFH);
		}

		// Now to check that setFilePathID successfully worked
		try {

			// Blank line
			System.out.println("");
			System.out.println("SearchString to match against is = "
					+ searchStringFH);
			// Blank line
			System.out.println("");
			System.out
					.println("Step 1: Checking SID returned by setSearchStringID"
							+ " contains same searchStringFH");

			// Run a select statement retrieving the searchString associated
			// with that SID
			System.out.println("Select * from searchString where SID = '" + SID
					+ "'");

			stmt = con
					.prepareStatement("Select * from searchString where SID = '"
							+ SID + "'");

			// Output row data where the slaveIP and data matches the test
			rs = stmt.executeQuery();
			if (rs.next()) {
				int numColumns = rs.getMetaData().getColumnCount();

				for (int i = 1; i <= numColumns; i++) {
					System.out.println("results: COLUMN " + "=" + " "
							+ rs.getObject(i));
				}

				System.out.println("END OF RESULTS");
				// Blank line
				System.out.println("");
			} else {
				System.out.println("FAILED: No match was found with SID");
				// Blank line
				System.out.println("");
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {

		}

		// Blank line
		System.out.println("");
		System.out.println("Step 3: Delete the row which has been entered "
				+ "with searchStringFH");
		// Delete new row from the results table
		stmt = con.prepareStatement("DELETE FROM searchString WHERE SID = '"
				+ SID + "'");

		recordsUpdated = stmt.executeUpdate();

		System.out.println("Number of fields deleted from results = "
				+ recordsUpdated);

		// Blank line
		System.out.println("");

		// Completed
		System.out.println("check_setSearchStringID COMPLETED");

		// Blank line
		System.out.println("");
		System.out.println("*******************");
		System.out.println("ALL TESTS COMPLETED");
		System.out.println("*******************");
	}

	@Before
	public void setup() {
		jdbc = new JDBC();

		// Load Driver
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		// Establish connection to database
		try {
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/c0350a", "c0350a", "fastddf");
		} catch (SQLException e) {
			System.out.println(e);
		}

	}

	@After
	public void teardown() {
		jdbc = null;
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
